CREATE VIEW [dbo].[vGift]
AS
SELECT
Activity.ID ID,
Activity.ORIGINATING_TRANS_NUM OriginalTransaction,
Activity.ORIGINATING_TRANS_NUM TransactionNumber,
max(Activity.SOURCE_SYSTEM) SourceSystem,
max(Activity.TRANSACTION_DATE) TransactionDate,
max(Activity.EFFECTIVE_DATE) DateReceived,
sum(Activity.AMOUNT) Amount,
sum(Activity.TAXABLE_VALUE) AS FairMktValue,
max(Activity.ORG_CODE) Fund,
max(Activity.SOURCE_CODE) Appeal,
max(Activity.SOLICITOR_ID) SolicitorID,
max(C.CHECK_NUMBER) CheckNumber,
(case max(Cash_Accounts.ACCOUNT_TYPE)
when 1 then 'Credit Card'
when 2 then 'In Kind'
when 3 then 'Debit Card'
else 'Cash' end) as PaymentType,
max(Activity.CAMPAIGN_CODE) Campaign,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),1,4)) as FiscalYear,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),5,2)) as FiscalMonth,
'GIFT' as GiftType,
max(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
max(C.RECEIPT_ID) ReceiptID,
max(C.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
max(C.IS_MATCH_GIFT) IsMatchingGift,
max(C.MEM_TRIB_ID) MemorialID,
'' as PledgeID,
max(Activity.ACTION_CODES) ListAs,
max(Activity.UF_4) RequestNumber,
null as InstallmentDate,
max(C.MEM_TRIB_NAME_TEXT) MemorialNameText,
CONVERT(money,sum(Activity.AMOUNT)) AmountAsMoney
FROM Activity
INNER JOIN Trans C on
Activity.ORIGINATING_TRANS_NUM = C.TRANS_NUMBER
INNER JOIN TransWatch ON
C.TRANS_NUMBER = TransWatch.TransactionNumber AND
C.INVOICE_REFERENCE_NUM = TransWatch.InvoiceNumber
LEFT OUTER JOIN Cash_Accounts ON
C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
WHERE
Activity.ACTIVITY_TYPE = 'GIFT' AND Activity.ID = C.ST_ID AND
(
(Activity.SOURCE_SYSTEM = 'FR' AND C.TRANSACTION_TYPE = 'PAY' AND C.JOURNAL_TYPE = 'IN' AND C.SUB_LINE_NUMBER = 1) OR
(Activity.SOURCE_SYSTEM IN ('SC', 'DUES') AND C.TRANSACTION_TYPE = 'DIST' AND C.PRODUCT_CODE = Activity.PRODUCT_CODE)
)
GROUP BY Activity.ID, Activity.ORIGINATING_TRANS_NUM, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE
UNION
SELECT
CASE WHEN Invoice_Lines.ST_ID IS NULL THEN Invoice.BT_ID ELSE Invoice_Lines.ST_ID END ID,
P.TRANS_NUMBER OriginalTransaction,
C.TRANS_NUMBER TransactionNumber,
max(P.SOURCE_SYSTEM) SourceSystem,
max(C.TRANSACTION_DATE)TransactionDate,
max(C.TRANSACTION_DATE) DateReceived,
(case when max(C.JOURNAL_TYPE) = 'IN' then sum(C.AMOUNT) else -sum(C.AMOUNT) end) Amount,
sum(C.TAXABLE_VALUE) AS FairMktValue,
(case when max(C.JOURNAL_TYPE) = 'IN' then max(C.OWNER_ORG_CODE) else max(C.GL_ACCT_ORG_CODE) end) Fund,
max(P.SOURCE_CODE)Appeal,
max(P.SOLICITOR_ID)SolicitorID,
max(C.CHECK_NUMBER)CheckNumber,
(case max(Cash_Accounts.ACCOUNT_TYPE)
when 1 then 'Credit Card'
when 2 then 'In Kind'
when 3 then 'Debit Card'
else 'Cash' end) as PaymentType,
max(Activity.CAMPAIGN_CODE)Campaign,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),1,4)) as FiscalYear,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),5,2)) as FiscalMonth,
'Pledge Payment' as GiftType,
max(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
max(C.RECEIPT_ID) ReceiptID,
max(P.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
max(P.IS_MATCH_GIFT) IsMatchingGift,
max(P.MEM_TRIB_ID) MemorialID,
(case when max(C.BT_ID)= max(C.ST_ID) then '' else max(C.BT_ID) end) as PledgeID,
max(Activity.ACTION_CODES) ListAs,
max(Activity.UF_4) RequestNumber,
max(Invoice.INSTALL_BILL_DATE) as InstallmentDate,
max(P.MEM_TRIB_NAME_TEXT) MemorialNameText,
CONVERT(money,(case when max(C.JOURNAL_TYPE) = 'IN' then sum(C.AMOUNT) else -sum(C.AMOUNT) end)) AmountAsMoney
FROM Invoice
INNER JOIN TransWatch ON
Invoice.ORIGINATING_TRANS_NUM = TransWatch.TransactionNumber AND
Invoice.REFERENCE_NUM = TransWatch.InvoiceNumber
INNER JOIN Trans P on
Invoice.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER
INNER JOIN Activity on
P.ACTIVITY_SEQN = Activity.SEQN
INNER JOIN Trans C ON
Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
LEFT OUTER JOIN Cash_Accounts on
C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
LEFT OUTER JOIN Invoice_Lines ON
C.INVOICE_REFERENCE_NUM = Invoice_Lines.REFERENCE_NUM AND
C.INVOICE_LINE_NUM = Invoice_Lines.LINE_NUM
WHERE
P.TRANSACTION_TYPE = 'DIST'
AND C.IS_FR_ITEM = 1
AND
(
( Activity.ACTIVITY_TYPE = 'PLEDGE' AND
P.LINE_NUMBER = 1 AND P.SUB_LINE_NUMBER = 1 AND Invoice.SOURCE_SYSTEM = 'FR' AND
(
(C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE = 'AR') OR
(C.JOURNAL_TYPE = 'IN' AND C.TRANSACTION_TYPE = 'PAY')
)
)
OR
( Activity.ACTIVITY_TYPE = 'PLEDGE' AND
Invoice.SOURCE_SYSTEM = 'DUES' AND
P.PRODUCT_CODE = C.PRODUCT_CODE AND
C.JOURNAL_TYPE = 'PAY' AND
C.TRANSACTION_TYPE = 'AR' AND
P.INVOICE_LINE_NUM = C.INVOICE_LINE_NUM
)
OR
( Activity.ACTIVITY_TYPE = 'GIFT' AND
P.LINE_NUMBER = 1 AND P.SUB_LINE_NUMBER = 1 AND
Invoice.SOURCE_SYSTEM = 'FR' AND
C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE = 'AR'
)
)
GROUP BY Invoice_Lines.ST_ID, Invoice.BT_ID, C.TRANS_NUMBER, C.INVOICE_REFERENCE_NUM, C.OWNER_ORG_CODE, C.GL_ACCT_ORG_CODE, Activity.CAMPAIGN_CODE, P.SOURCE_CODE, P.TRANS_NUMBER
UNION
SELECT
Activity.ID ID,
Invoice.ORIGINATING_TRANS_NUM as OriginalTransaction,
0 as TransactionNumber,
max(Activity.SOURCE_SYSTEM) SourceSystem,
max(Activity.TRANSACTION_DATE) TransactionDate,
max(Activity.EFFECTIVE_DATE) DateReceived,
sum(Activity.AMOUNT) Amount,
sum(C.TAXABLE_VALUE) AS FairMktValue,
max(Activity.ORG_CODE) Fund,
max(Activity.SOURCE_CODE) Appeal,
'' as SolicitorID,
'' as CheckNumber,
'' as PaymentType,
max(Activity.CAMPAIGN_CODE) Campaign,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),1,4)) as FiscalYear,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),5,2)) as FiscalMonth,
'GIFT' as GiftType,
max(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
0 as ReceiptID,
0 as MatchingTransaction,
0 as IsMatchingGift,
'' as MemorialID,
'' as PledgeID,
'' as ListAs,
0 as RequestNumber,
null as InstallmentDate,
'' as MemorialNameText,
CONVERT(money,sum(Activity.AMOUNT)) AmountAsMoney
FROM Activity
INNER JOIN Trans C on
Activity.ORIGINATING_TRANS_NUM = C.TRANS_NUMBER
INNER JOIN Invoice on
Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
INNER JOIN TransWatch ON
C.TRANS_NUMBER = TransWatch.TransactionNumber AND
C.INVOICE_REFERENCE_NUM = TransWatch.InvoiceNumber
WHERE
Activity.ACTIVITY_TYPE = 'GIFT' AND
C.TRANSACTION_TYPE = 'DIST' AND
(C.PRODUCT_CODE = Activity.PRODUCT_CODE OR C.PRODUCT_CODE LIKE Activity.PRODUCT_CODE +'/%') AND
Activity.SOURCE_SYSTEM = 'MEETING'
GROUP BY Activity.ID, C.INVOICE_REFERENCE_NUM, Activity.ORG_CODE, Activity.CAMPAIGN_CODE, Activity.SOURCE_CODE, Invoice.ORIGINATING_TRANS_NUM
UNION
SELECT
max(P.ST_ID) ID,
max(P.TRANS_NUMBER) OriginalTransaction,
max(C.TRANS_NUMBER) TransactionNumber,
max(P.SOURCE_SYSTEM) SourceSystem,
max(C.TRANSACTION_DATE)TransactionDate,
max(C.TRANSACTION_DATE) DateReceived,
-sum(C.AMOUNT) Amount,
sum(P.TAXABLE_VALUE) AS FairMktValue,
(
case when max(C.JOURNAL_TYPE) = 'IN'
then max(C.OWNER_ORG_CODE)
else max(C.GL_ACCT_ORG_CODE) end
) Fund,
max(P.SOURCE_CODE)Appeal,
max(P.SOLICITOR_ID)SolicitorID,
max(C.CHECK_NUMBER)CheckNumber,
(
case max(Cash_Accounts.ACCOUNT_TYPE)
when 1 then 'Credit Card'
when 2 then 'In Kind'
when 3 then 'Debit Card'
else 'Cash' end
) PaymentType,
max(Activity.CAMPAIGN_CODE)Campaign,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),1,4)) as FiscalYear,
convert(int,substring(convert(char(6),max(C.FISCAL_PERIOD)),5,2)) as FiscalMonth,
'Asterisk Tab Adjustment' as GiftType,
max(C.INVOICE_REFERENCE_NUM) InvoiceReferenceNumber,
max(C.RECEIPT_ID) ReceiptID,
max(P.MATCH_GIFT_TRANS_NUM) MatchingTransaction,
max(P.IS_MATCH_GIFT) IsMatchingGift,
max(P.MEM_TRIB_ID) MemorialID,
(case when max(C.BT_ID)= max(C.ST_ID) then '' else max(C.BT_ID) end) as PledgeID,
'' ListAs,
0 RequestNumber,
max(Invoice.INSTALL_BILL_DATE) as InstallmentDate,
max(P.MEM_TRIB_NAME_TEXT) MemorialNameText,
CONVERT(money,-sum(C.AMOUNT)) AmountAsMoney
FROM Invoice
INNER JOIN TransWatch ON
Invoice.ORIGINATING_TRANS_NUM = TransWatch.TransactionNumber AND
Invoice.REFERENCE_NUM = TransWatch.InvoiceNumber
INNER JOIN Trans C ON
Invoice.REFERENCE_NUM = C.INVOICE_REFERENCE_NUM
LEFT OUTER JOIN Cash_Accounts on
C.CHECK_NUMBER = Cash_Accounts.CASH_ACCOUNT_CODE
INNER JOIN Trans P ON
Invoice.ORIGINATING_TRANS_NUM = P.TRANS_NUMBER
INNER JOIN Activity on
P.ACTIVITY_SEQN = Activity.SEQN
WHERE
P.LINE_NUMBER = 1 AND P.SUB_LINE_NUMBER = 1 AND P.JOURNAL_TYPE = 'IN' AND
P.TRANSACTION_TYPE = 'DIST' AND Invoice.SOURCE_SYSTEM='FR' AND
(
(C.JOURNAL_TYPE = 'IN' AND C.TRANSACTION_TYPE = 'TR') OR
(C.JOURNAL_TYPE = 'PAY' AND C.TRANSACTION_TYPE IN ('PAY', 'TR'))
)
GROUP BY P.ST_ID, C.TRANS_NUMBER, P.INVOICE_REFERENCE_NUM
UNION
SELECT ID,
OriginalTransaction,
GiftReport.TransactionNumber,
SourceSystem,
TransactionDate,
DateReceived,
Amount,
FairMktValue,
Fund,
AppealCode,
SolicitorID,
CheckNumber,
PaymentType,
CampaignCode,
FiscalYear,
FiscalMonth,
GiftType,
InvoiceReferenceNumber,
ReceiptID,
MatchingTransaction,
IsMatchingGift,
MemorialID,
PledgeID,
ListAs,
RequestNumber,
InstallmentDate,
MemorialNameText,
CONVERT(money,Amount) as AmountAsMoney
FROM GiftReport
LEFT OUTER JOIN TransWatch ON
GiftReport.OriginalTransaction = TransWatch.TransactionNumber AND
GiftReport.InvoiceReferenceNumber = TransWatch.InvoiceNumber
WHERE
TransWatch.TransactionNumber IS NULL
UNION
SELECT ID,
OriginalTransaction,
GiftReport.TransactionNumber,
SourceSystem,
TransactionDate,
DateReceived,
Amount,
FairMktValue,
Fund,
AppealCode,
SolicitorID,
CheckNumber,
PaymentType,
CampaignCode,
FiscalYear,
FiscalMonth,
GiftType,
InvoiceReferenceNumber,
ReceiptID,
MatchingTransaction,
IsMatchingGift,
MemorialID,
PledgeID,
ListAs,
RequestNumber,
InstallmentDate,
MemorialNameText,
CONVERT(money,Amount) AmountAsMoney
FROM GiftReport
WHERE
OriginalTransaction NOT IN (SELECT TransactionNumber FROM TransWatch) AND
SourceSystem = 'MEETING'
GO
GRANT REFERENCES ON [dbo].[vGift] TO [IMIS]
GRANT SELECT ON [dbo].[vGift] TO [IMIS]
GRANT INSERT ON [dbo].[vGift] TO [IMIS]
GRANT DELETE ON [dbo].[vGift] TO [IMIS]
GRANT UPDATE ON [dbo].[vGift] TO [IMIS]
GO
[dbo].[vGift]